
const mysql = require("mysql2/promise");
// 创建数据库连接池
const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "root",
  database: "multiple_spiders",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

// 初始化数据库
async function initDatabase() {
  const conn = await pool.getConnection();
  try {
    await conn.query(`
          CREATE TABLE IF NOT EXISTS \`douyin_video\` (
          \`id\` int(10) unsigned NOT NULL AUTO_INCREMENT,
          \`crawl_time\` datetime NOT NULL COMMENT '抓取时间',
          \`item_id\` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '抖音视频id',
          \`category\` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频分类',
          \`cover\` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频封面图',
          \`publish_time\` datetime NOT NULL COMMENT '视频发布时间',
          \`title\` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '视频标题',
          \`collect_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '视频收藏数',
          \`comment_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '评论数',
          \`share_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '分享数',
          \`play_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '播放量',
          \`finish\` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '完播量',
          \`finish5s\` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '5s完播量',
          \`increase_fans_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '粉丝增长数',
          \`like_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '点赞量',
          \`play_avg_time\` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '平均播放时长',
          \`skip2s\` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '跳过2s',
          \`visit_count\` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '主页访问量',
          \`video_path\` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '详情页路径',
          PRIMARY KEY (\`id\`),
          UNIQUE KEY \`item_id\` (\`item_id\`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
      `);
  } finally {
    conn.release();
  }
}

module.exports = {
  pool,
  initDatabase,
};
